﻿using Magicodes.Logger;
using Magicodes.Logger.NLog;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TXH.DataSync.Helper
{
    public static class DbHelper
    {
        public static LoggerBase Logger = new NLogLogger("DbHelper");
        private static string ConnectionString
        {
            get { return ConfigHelper.ConfigInfo.ConnectionString; }
        }
        public static DbProviderFactory Factory = DbProviderFactories.GetFactory("System.Data.SqlClient");

        public static DbConnection CreateConnection()
        {
            DbConnection con = Factory.CreateConnection();
            con.ConnectionString = ConnectionString;
            return con;
        }

        #region 参数

        public static DbParameter CreateParameter(DbParameter param)
        {
            return CreateParameter(param.ParameterName, param.Value, param.DbType, param.Size, param.Direction, param.SourceColumn, param.SourceColumnNullMapping, param.SourceVersion);
        }

        public static DbParameter CreateParameter(string ParameterName, object Value, DbType? DbType = null, int? Size = null, ParameterDirection? Direction = null, string SourceColumn = null, bool? SourceColumnNullMapping = null, DataRowVersion? SourceVersion = null)
        {
            DbParameter param = Factory.CreateParameter();

            param.ParameterName = ParameterName;
            param.Value = Value;

            if (DbType != null)
                param.DbType = DbType.Value;
            if (Size != null)
                param.Size = Size.Value;
            if (Direction != null)
                param.Direction = Direction.Value;
            if (SourceColumn != null)
                param.SourceColumn = SourceColumn;
            if (SourceColumnNullMapping != null)
                param.SourceColumnNullMapping = SourceColumnNullMapping.Value;
            if (SourceVersion != null)
                param.SourceVersion = SourceVersion.Value;

            return param;
        }

        private static DbParameter[] ConvertParameters(object[] parameters)
        {
            List<DbParameter> paramList = new List<DbParameter>();

            for (int i = 0; i < parameters.Length; i++)
            {
                if (parameters[i] is DbParameterCollection)
                    foreach (DbParameter item in parameters[i] as DbParameterCollection) paramList.Add(CreateParameter(item));
                else if (parameters[i] is DbParameter)
                    paramList.Add(parameters[i] as DbParameter);
                else
                    paramList.Add(CreateParameter("@" + i, parameters[i]));
            }

            return paramList.ToArray();
        }

        #endregion

        public static Query Query(string query, params object[] parameters)
        {
            return new Query(query, ConvertParameters(parameters));
        }

        public static bool Insert(string table, object model)
        {
            StringBuilder fields = new StringBuilder();
            StringBuilder values = new StringBuilder();
            List<DbParameter> paramList = new List<DbParameter>();

            foreach (var item in model.GetType().GetProperties())
            {
                fields.AppendFormat("[{0}],", item.Name);
                values.AppendFormat("@{0},", item.Name);
                paramList.Add(CreateParameter("@" + item.Name, item.GetValue(model, null)));
            }

            return DbHelper.Query(string.Format("insert into [{0}]({1}) values({2})", table, fields.ToString().TrimEnd(','), values.ToString().TrimEnd(',')), paramList.ToArray()).Execute() > 0;
        }

        public static bool Update(string table, object model, string where, params object[] parameters)
        {
            StringBuilder fieldsAndValues = new StringBuilder();
            List<DbParameter> paramList = new List<DbParameter>();

            foreach (var item in model.GetType().GetProperties())
            {
                fieldsAndValues.AppendFormat("[{0}]=@{0},", item.Name);
                paramList.Add(CreateParameter("@" + item.Name, item.GetValue(model, null)));
            }

            paramList.AddRange(ConvertParameters(parameters));

            return DbHelper.Query(string.Format("update [{0}] set {1}", table, fieldsAndValues.ToString().TrimEnd(',') + ((where ?? "").Trim() == "" ? "" : " where " + where)), paramList.ToArray()).Execute() > 0;
        }

        public static bool Update(string table, string set, string where, params object[] parameters)
        {
            StringBuilder fieldsAndValues = new StringBuilder();
            List<DbParameter> paramList = new List<DbParameter>();

            fieldsAndValues.Append(set);

            paramList.AddRange(ConvertParameters(parameters));

            return DbHelper.Query(string.Format("update [{0}] set {1}", table, fieldsAndValues.ToString().TrimEnd(',') + ((where ?? "").Trim() == "" ? "" : " where " + where)), paramList.ToArray()).Execute() > 0;
        }
    }

    public class Query
    {
        #region 构造方法

        public Query(string query, DbParameter[] parameters)
        {
            SqlQuery = query;
            Parameters = parameters;
        }
        #endregion

        #region 属性/字段

        public string SqlQuery { get; set; }
        public DbParameter[] Parameters { get; set; }

        #endregion

        #region 执行基础

        private T ExecuteCommon<T>(Func<DbCommand, T> function)
        {
            using (DbConnection con = DbHelper.CreateConnection())
            using (DbCommand cmd = con.CreateCommand())
            {
                cmd.CommandText = SqlQuery;
                cmd.Parameters.AddRange(Parameters);
                con.Open();
                T result = function(cmd);
                cmd.Parameters.Clear();
                return result;
            }
        }

        public T Execute<T>(Func<DbCommand, T> function)
        {
            return ExecuteCommon<T>(function);
        }

        public void Execute(Action<DbCommand> action)
        {
            Execute(cmd => { action(cmd); return 0; });
        }

        #endregion

        #region 执行查询

        public int Execute()
        {
            return Execute(cmd => cmd.ExecuteNonQuery());
        }

        public object Scalar()
        {
            return Execute(cmd => cmd.ExecuteScalar());
        }

        public T Scalar<T>()
        {
            return Execute(cmd => (T)cmd.ExecuteScalar());
        }

        public Query Top(int count)
        {
            return DbHelper.Query(string.Format("select top {0} * from ({1}) as t0", count, SqlQuery), Parameters);
        }

        public Single ToSingle()
        {
            return Execute(cmd =>
            {
                Single s = new Single();

                using (var dr = cmd.ExecuteReader())
                {
                    if (dr.Read())
                    {
                        string name = string.Empty;

                        for (int i = 0; i < dr.FieldCount; i++)
                        {
                            name = dr.GetName(i);
                            s[name] = dr[name];
                        }
                    }
                    else
                    {
                        throw new Exception("Not Find !!");
                    }
                }

                return s;
            });

        }

        public DataTable ToDataTable()
        {
            return Execute(cmd =>
            {
                DbDataAdapter da = DbHelper.Factory.CreateDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            });
        }

        public List<T> ToList<T>()
        {
            return Execute(cmd =>
            {
                List<T> list = new List<T>();

                using (var dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        Type t = typeof(T);
                        T s = default(T);
                        string name = string.Empty;

                        for (int i = 0; i < dr.FieldCount; i++)
                        {
                            name = dr.GetName(i);
                            var pro = t.GetProperty(name);

                            if (pro != null)
                                pro.SetValue(s, dr[name], null);
                        }

                        list.Add(s);
                    }
                }

                return list;
            });
        }

        public override string ToString()
        {
            return Scalar<string>();
        }

        #endregion

        #region 分页

        private Query RecordCountQuery
        {
            get { return DbHelper.Query(string.Format("select count(*) from ({0}) as t0", SqlQuery), Parameters); }
        }

        private Query PagerResultQuery(string primaryKey, int pageIndex, int pageSize)
        {
            return DbHelper.Query(string.Format("select top {1} * from ({0}) as t0" +
                (pageIndex > 1 ? " where t0.{3} not in (select top {2} t1.{3} from ({0}) as t1)" : ""),
                SqlQuery, pageSize, pageIndex * pageSize, primaryKey), Parameters);
        }

        public DataTable ToPager(string primaryKey, int pageIndex, int pageSize, Action<int> recordCount)
        {
            recordCount(RecordCountQuery.Scalar<int>());
            return PagerResultQuery(primaryKey, pageIndex, pageSize).ToDataTable();
        }

        public DataTable ToPager(int pageIndex, int pageSize, Action<int> recordCount)
        {
            return ToPager("Id", pageIndex, pageSize, recordCount);
        }

        public List<T> ToPager<T>(string primaryKey, int pageIndex, int pageSize, Action<int> recordCount)
        {
            recordCount(RecordCountQuery.Scalar<int>());
            return PagerResultQuery(primaryKey, pageIndex, pageSize).ToList<T>();
        }

        public List<T> ToPager<T>(int pageIndex, int pageSize, Action<int> recordCount)
        {
            return ToPager<T>("Id", pageIndex, pageSize, recordCount);
        }

        #endregion
    }

    public class Single : Dictionary<string, object>
    {
        public new object this[string name]
        {
            get { return base[name.ToLower()]; }
            set { Add(name.ToLower(), value); }
        }
    }
}
